"""数据库模型定义"""

from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime, ForeignKey, JSON, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
from datetime import datetime

# 创建基类
Base = declarative_base()

class Camera(Base):
    """摄像头信息表"""
    __tablename__ = 'cameras'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False, unique=True)
    description = Column(Text, nullable=True)
    url = Column(String(255), nullable=False)  # RTSP URL或视频文件路径
    camera_type = Column(String(50), nullable=False, default='rtsp')  # rtsp, local, file
    location = Column(String(255), nullable=True)
    latitude = Column(Float, nullable=True)
    longitude = Column(Float, nullable=True)
    status = Column(String(20), nullable=False, default='inactive')  # active, inactive, disconnected
    is_enabled = Column(Boolean, default=True)
    roi = Column(JSON, nullable=True)  # 感兴趣区域坐标
    fps = Column(Float, nullable=True)
    resolution = Column(String(20), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    config = Column(JSON, nullable=True)  # 摄像头特定配置
    
    def __repr__(self):
        return f"<Camera(name='{self.name}', url='{self.url}')>"

class Detection(Base):
    """目标检测记录表"""
    __tablename__ = 'detections'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    camera_id = Column(Integer, ForeignKey('cameras.id'), nullable=False)
    frame_id = Column(String(50), nullable=False)
    object_id = Column(Integer, nullable=False)
    class_id = Column(Integer, nullable=False)
    class_name = Column(String(50), nullable=False)
    confidence = Column(Float, nullable=False)
    x1 = Column(Float, nullable=False)  # 边界框左上角x坐标
    y1 = Column(Float, nullable=False)  # 边界框左上角y坐标
    x2 = Column(Float, nullable=False)  # 边界框右下角x坐标
    y2 = Column(Float, nullable=False)  # 边界框右下角y坐标
    width = Column(Float, nullable=False)
    height = Column(Float, nullable=False)
    area = Column(Float, nullable=False)
    center_x = Column(Float, nullable=False)
    center_y = Column(Float, nullable=False)
    tracking_id = Column(Integer, nullable=True)
    attributes = Column(JSON, nullable=True)  # 其他属性，如颜色、姿态等
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    def __repr__(self):
        return f"<Detection(class='{self.class_name}', confidence={self.confidence})>"

class Tracking(Base):
    """目标跟踪记录表"""
    __tablename__ = 'tracking'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    camera_id = Column(Integer, ForeignKey('cameras.id'), nullable=False)
    tracking_id = Column(Integer, nullable=False)
    class_id = Column(Integer, nullable=False)
    class_name = Column(String(50), nullable=False)
    start_time = Column(DateTime(timezone=True), server_default=func.now())
    end_time = Column(DateTime(timezone=True), nullable=True)
    duration = Column(Float, nullable=True)  # 跟踪持续时间（秒）
    start_x = Column(Float, nullable=False)
    start_y = Column(Float, nullable=False)
    end_x = Column(Float, nullable=True)
    end_y = Column(Float, nullable=True)
    path = Column(JSON, nullable=True)  # 轨迹点列表
    speed_avg = Column(Float, nullable=True)  # 平均速度
    is_active = Column(Boolean, default=True)
    attributes = Column(JSON, nullable=True)
    
    def __repr__(self):
        return f"<Tracking(id={self.tracking_id}, class='{self.class_name}')>"

class Violation(Base):
    """违规行为记录表"""
    __tablename__ = 'violations'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    camera_id = Column(Integer, ForeignKey('cameras.id'), nullable=False)
    tracking_id = Column(Integer, nullable=True)
    detection_id = Column(Integer, ForeignKey('detections.id'), nullable=True)
    violation_type = Column(String(50), nullable=False)  # zone_violation, wrong_direction, illegal_parking, pedestrian_violation, vehicle_overlap
    severity = Column(Float, nullable=False)  # 严重程度 0.0-1.0
    description = Column(Text, nullable=True)
    image_path = Column(String(255), nullable=True)
    video_clip_path = Column(String(255), nullable=True)
    location = Column(String(255), nullable=True)
    latitude = Column(Float, nullable=True)
    longitude = Column(Float, nullable=True)
    status = Column(String(20), default='unhandled')  # unhandled, processed, dismissed
    handler = Column(String(50), nullable=True)
    handled_at = Column(DateTime(timezone=True), nullable=True)
    details = Column(JSON, nullable=True)  # 详细违规信息
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    def __repr__(self):
        return f"<Violation(type='{self.violation_type}', severity={self.severity})>"

class Alert(Base):
    """告警记录表"""
    __tablename__ = 'alerts'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    alert_id = Column(String(50), nullable=False, unique=True)  # UUID
    alert_type = Column(String(50), nullable=False)
    severity = Column(String(20), nullable=False)  # info, warning, error, critical
    source = Column(String(50), nullable=False)
    message = Column(Text, nullable=True)
    details = Column(JSON, nullable=True)
    snapshot_path = Column(String(255), nullable=True)
    is_acknowledged = Column(Boolean, default=False)
    acknowledged_by = Column(String(50), nullable=True)
    acknowledged_at = Column(DateTime(timezone=True), nullable=True)
    is_resolved = Column(Boolean, default=False)
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # 关联到违规记录
    violation_id = Column(Integer, ForeignKey('violations.id'), nullable=True)
    
    def __repr__(self):
        return f"<Alert(type='{self.alert_type}', severity='{self.severity}')>"

class SystemLog(Base):
    """系统日志表"""
    
    __tablename__ = 'system_logs'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    level = Column(String(20), nullable=False)  # debug, info, warning, error, critical
    component = Column(String(50), nullable=False)  # 组件名称
    message = Column(Text, nullable=False)
    details = Column(JSON, nullable=True)
    stack_trace = Column(Text, nullable=True)
    log_metadata = Column(JSON, nullable=True)  # 元数据，存储额外的上下文信息
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    def __repr__(self):
        return f"<SystemLog(level='{self.level}', component='{self.component}')>"

class User(Base):
    """用户信息表"""
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False, unique=True)
    password_hash = Column(String(255), nullable=False)
    email = Column(String(100), nullable=True, unique=True)
    phone = Column(String(20), nullable=True)
    role = Column(String(20), nullable=False, default='viewer')  # admin, operator, viewer
    status = Column(String(20), nullable=False, default='active')  # active, inactive, locked
    last_login = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    preferences = Column(JSON, nullable=True)
    
    def __repr__(self):
        return f"<User(username='{self.username}', role='{self.role}')>"

class AlertHistory(Base):
    """告警历史记录表"""
    __tablename__ = 'alert_history'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    alert_id = Column(Integer, ForeignKey('alerts.id'), nullable=False)
    status = Column(String(50), nullable=False)
    timestamp = Column(DateTime(timezone=True), server_default=func.now())
    message = Column(Text, nullable=True)
    details = Column(JSON, nullable=True)
    
    def __repr__(self):
        return f"<AlertHistory(alert_id={self.alert_id}, status='{self.status}')>"

class Config(Base):
    """系统配置表"""
    __tablename__ = 'configs'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    key = Column(String(100), nullable=False, unique=True)
    value = Column(JSON, nullable=False)
    description = Column(Text, nullable=True)
    config_type = Column(String(50), nullable=False, default='general')  # general, model, tracking, analysis, alert
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    def __repr__(self):
        return f"<Config(key='{self.key}', type='{self.config_type}')>"